/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package util;

import hxg.portmgr.core.dao.DaoSource;
import hxg.portmgr.core.device.Attr;
import hxg.portmgr.core.device.Device;
import hxg.portmgr.core.device.DeviceType;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

/**
 *
 * @author hxg
 */
public class ExcelUilts {

    private static final Logger logger = Logger.getLogger(ExcelUilts.class.getName());

    public static void synDeviceAttrFromExcel(File f) throws IOException, Exception {

        LinkedHashMap<String, LinkedHashMap<String, String>> attrMap = splitExcelFileToAttrMap(f);
        for (String ip : attrMap.keySet()) {
            Device dev = DaoSource.getDeviceDao().getDeviceByIp(ip);
            try {
                synAttrMap2Device(dev, attrMap.get(ip));
            } catch (Exception e) {
                logger.log(Level.WARNING, "同步设备{0}[IP={1}]时数据异常,异常{2}", new Object[]{dev,dev.getIp(),e});
            }
        }
    }

    private static LinkedHashMap<String, LinkedHashMap<String, String>> splitExcelFileToAttrMap(File f) throws IOException {
        LinkedHashMap<String, LinkedHashMap<String, String>> devAttrMap = new LinkedHashMap();

        POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(f));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        int firstrownum = sheet.getFirstRowNum();
        int lastrownum = sheet.getLastRowNum();
        //先读取第一行作为属性名
        HSSFRow row1 = sheet.getRow(firstrownum);
        //属性列值对应表
        LinkedHashMap<Integer, String> attrNameMap = new LinkedHashMap();

        //<editor-fold defaultstate="collapsed" desc="读取属性名[第一行]">
        for (int k = row1.getFirstCellNum(); k < row1.getLastCellNum(); k++) {
            HSSFCell cell = row1.getCell(k);
            String value;
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        value = cell.getRichStringCellValue().getString();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        value = Double.toString(cell.getNumericCellValue());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.getRichStringCellValue().getString();
                        break;
                }
            } else {
                value = "";
            }
            attrNameMap.put(k, value);
            //valueList.add(value);
        }
        //</editor-fold>
        //第一行以后的都是值行了

        for (int j = firstrownum + 1; j <= lastrownum; j++) {
            //用try包含是为了在发现异常时丢弃有问题的行而不影响其它行            
            LinkedHashMap<String, String> attrMap = new LinkedHashMap(lastrownum - firstrownum);
            try {
                HSSFRow row = sheet.getRow(j);
                for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
                    HSSFCell cell = row.getCell(k);
                    String value ;
                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                                value = cell.getRichStringCellValue().getString();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                value = Double.toString(cell.getNumericCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            default:
                                value = cell.getRichStringCellValue().getString();
                                break;
                        }
                    } else {
                        value = "";
                    }
                    String attrName = attrNameMap.get(k);
                    attrMap.put(attrName, value);
                }
                String ip = attrMap.get("ip");
                devAttrMap.put(ip, attrMap);
            } catch (Exception ex) {
                Logger.getLogger(Device.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return devAttrMap;
    }

    private static void synAttrMap2Device(Device dev, LinkedHashMap<String, String> mp) {
        HashSet<String> set = new HashSet();
        LinkedHashMap<String, String> attrMap = new LinkedHashMap();
        if (dev != null) {
            Field[] selffields = dev.getClass().getDeclaredFields();
            Field[] fatherFields = dev.getClass().getSuperclass().getDeclaredFields();
            Field[] fields = new Field[selffields.length + fatherFields.length];
            System.arraycopy(selffields, 0, fields, 0, selffields.length);
            System.arraycopy(fatherFields, 0, fields, selffields.length, fatherFields.length);
            for (Field field : fields) {
                Attr attr = field.getAnnotation(Attr.class);
                if (attr != null) {
                    String chsname = attr.chsName();
                    set.add(chsname);
                    if (mp.containsKey(chsname)) {
                        String value = mp.get(chsname);
                        try {
                            field.setAccessible(true);

                            if (field.getType() == String.class) {
                                field.set(dev, value);
                            } else if (field.getType() == int.class) {
                                double d = Double.parseDouble(value);
                                field.setInt(dev, (int) d);
                            } else if (field.getType() == long.class) {
                                double d = Double.parseDouble(value);
                                field.setLong(dev, (long) d);
                            } else if (field.getType() == double.class) {
                                field.setDouble(dev, Double.parseDouble(value));
                            } else if (field.getType() == float.class) {
                                double d = Double.parseDouble(value);
                                field.setFloat(dev, (float) d);
                            } else {
                                System.out.println(field.getType());
                                field.set(dev, value);
                            }
                            logger.log(Level.INFO, "同步设备{0}的域{1}[{2}]属性为{3}", new Object[]{dev, field, chsname, field.get(dev)});
                        } catch (IllegalArgumentException ex) {
                            logger.log(Level.SEVERE, "同步设备{0}属性{1}失败，原因{2}", new Object[]{dev, chsname, ex.getMessage()});
                        } catch (IllegalAccessException ex) {
                            logger.log(Level.SEVERE, "同步设备{0}属性{1}失败，原因{2}", new Object[]{dev, chsname, ex.getMessage()});
                        }
                    }
                }
            }
            for (String key : mp.keySet()) {
                if (!set.contains(key)) {
                    attrMap.put(key, mp.get(key));
                }
            }
            dev.synAttr(attrMap);
        }
    }

    private static void exportDeviceToExcelSheet(Collection<Device> devs, HSSFSheet sheet) {
        ArrayList<LinkedHashMap<String, String>> valuelist = new ArrayList();
        //生成MAP
        for (Device dev : devs) {
            LinkedHashMap<String, String> mp = new LinkedHashMap();
            {
                Field[] devfields = Device.class.getDeclaredFields();
                for (Field field : devfields) {
                    Attr attr = field.getAnnotation(Attr.class);
                    if (attr != null) {
                        try {
                            field.setAccessible(true);
                            String value = field.get(dev).toString();
                            mp.put(attr.chsName(), value);
                        } catch (Exception ex) {
                            logger.log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
//            if (c != Device.class) {
//                Field[] devfields = c.getDeclaredFields();
//                for (Field field : devfields) {
//                    Attr attr = field.getAnnotation(Attr.class);
//                    if (attr != null) {
//                        try {
//                            field.setAccessible(true);
//                            Object obj = field.get(dev);
//                            String value = obj == null ? "" : obj.toString();
//                            mp.put(attr.chsName(), value);
//                        } catch (IllegalArgumentException ex) {
//                            logger.log(Level.SEVERE, null, ex);
//                        } catch (IllegalAccessException ex) {
//                            logger.log(Level.SEVERE, null, ex);
//                        } catch (Exception ex) {
//                            logger.log(Level.SEVERE, null, ex);
//                        }
//                    }
//                }
//            }
            mp.putAll(dev.getAttrMap());
            valuelist.add(mp);
        }
        //创建head,写入文件
        LinkedHashSet<String> headset = new LinkedHashSet();
        for (LinkedHashMap<String, String> mp : valuelist) {
            headset.addAll(mp.keySet());
        }
        ArrayList<String> headlist = new ArrayList(headset);
        HSSFRow hrow = sheet.createRow(0);
        for (int i = 0; i < headlist.size(); i++) {
            HSSFCell cell = hrow.createCell(i);
            cell.setCellValue(headlist.get(i));
        }
        //创建数据,写入文件
        for (int i = 0; i < valuelist.size(); i++) {
            HSSFRow row = sheet.createRow(i + 1);
            LinkedHashMap<String, String> mp = valuelist.get(i);
            for (int j = 0; j < headlist.size(); j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(mp.get(headlist.get(j)));
            }
        }
    }

    public static void exportDevicesExcel(Collection<Device> devs, File f) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        Map<DeviceType, Collection<Device>> devMp = new TreeMap();
        for (Device dev : devs) {
            if (!devMp.containsKey(dev.getType())) {
                devMp.put(dev.getType(), new TreeSet<Device>());
            }
            devMp.get(dev.getType()).add(dev);
        }
        for (DeviceType type : devMp.keySet()) {
            HSSFSheet sheet = wb.createSheet(type.toString());
            exportDeviceToExcelSheet(devMp.get(type), sheet);
        }
        FileOutputStream os = null;
        try {
            os = new FileOutputStream(f);
            wb.write(os);
        } catch (Exception e) {
            logger.log(Level.WARNING, e.getMessage());
        } finally {
            os.close();
        }

    }

    public static void exportDeviceIntfToExcel(File f, Collection<Device> devs) {
    }
    
    
}
